﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DBList;
using DBList.Entities;

namespace DBAccessor.Accessor
{
    public class MemberAccessor
    {
        //登陆验证
        public UInt64 Validate(string userName, string password)
        {
            UInt64 nRet = 0;
			try	{
				SqlParameter[] sqlParameter = {
                 new SqlParameter("@username",SqlDbType.NVarChar,20),
                 new SqlParameter("@userpwd",SqlDbType.NVarChar,50),
                 new SqlParameter("@outuserid",SqlDbType.Int,4),
                 new SqlParameter("@outgroupid",SqlDbType.Int,4)
	            };
				sqlParameter[0].Value = userName;
				sqlParameter[1].Value = password;
				sqlParameter[2].Value = 0;
				sqlParameter[2].Direction = ParameterDirection.Output;
				sqlParameter[3].Value = 0;
				sqlParameter[3].Direction = ParameterDirection.Output;
				Int32 nID = 0, nGroupId = 0;
				SqlCommand cmd = new SqlCommand();
				SqlHelper.ExecuteNonQuery(cmd, "User_Validate", sqlParameter);
				if (cmd.Parameters["@outuserid"].Value != null)
					Int32.TryParse(cmd.Parameters["@outuserid"].Value.ToString(), out nID);
				if (cmd.Parameters["@outgroupid"].Value != null)
					Int32.TryParse(cmd.Parameters["@outgroupid"].Value.ToString(), out nGroupId);
				nRet = (UInt64)(((Int64)nID << 32) | nGroupId);
				cmd.Parameters.Clear();
			}
			catch (System.Exception e){
				String strErr = e.Message;
				nRet = 0;
			}
            return nRet;
        }

        public UserData GetMemberInfo(int userid)
        {
            SqlParameter[] sqlParameter = {
                 new SqlParameter("@msgid",SqlDbType.Int,4)
            };
            sqlParameter[0].Value = userid;
            UserData	info = new UserData();
            SqlDataReader read = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "GetMemberInfo", sqlParameter);
			if (read.HasRows && read.Read())
			{
				int nID = 0;
				string strName = read["MemberName"].ToString();
				int.TryParse(read["MemberId"].ToString(), out nID);
				if ((strName != null && strName.Length > 0) || nID != 0)
				{
					info.MemberId = nID;
					info.MemberName = strName;
					info.RealName = read["RealName"].ToString();
					info.Email = read["Email"].ToString();
					info.Phone = read["Phone"].ToString();
					info.Address = read["Address"].ToString();
					info.Introduction = read["Introduction"].ToString();

					DateTime dt;
					strName = read["DateTime"].ToString();
					if (DateTime.TryParse(strName, out dt))
						info.AddDate = dt;

					int nState = 0;
					strName = read["State"].ToString();
					info.StateName = @"正常";
					if (int.TryParse(strName, out nState) && nState > 10)
						info.StateName = @"关闭";
					info.AvailableState = nState;

					nState = 0;
					strName = read["GroupId"].ToString();
					int.TryParse(strName, out nState);
					info.GroupId = nState;
				}
            }
			read.Close();
            read.Dispose();
            return info;
        }

		public bool ValidateLoginName(String strname)
		{
			bool bExist = true;
			SqlParameter[] sqlParameter = { 
                new SqlParameter("@name",SqlDbType.NVarChar,20)
            };
			sqlParameter[0].Value = strname;
			using (SqlConnection sqlConnection = new SqlConnection())
			{
				sqlConnection.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;

				object obj = SqlHelper.ExecuteScalar(sqlConnection, CommandType.StoredProcedure, "LoginNameValidate", sqlParameter);
				if (obj != null){
					int nCount = int.Parse(obj.ToString());
					if (nCount < 1) bExist = false;
				}
			}
			return bExist;
		}

		public int UpdateMember(UserData memberData)
		{
			int nRet = -10;
			try
			{
				SqlParameter[] sqlParameters = {
					new SqlParameter("@memberID", SqlDbType.Int, 4),
					new SqlParameter("@MemberName", SqlDbType.NVarChar),
					new SqlParameter("@MemberPass", SqlDbType.NVarChar),
					new SqlParameter("@RealName", SqlDbType.NVarChar, 20),
					new SqlParameter("@Phone", SqlDbType.NVarChar, 20),
					new SqlParameter("@Introduction", SqlDbType.NVarChar, 80),
					new SqlParameter("@Address", SqlDbType.NVarChar, 50),
					new SqlParameter("@Email", SqlDbType.NVarChar, 20),
					new SqlParameter("@groupid", SqlDbType.Int, 4),
					new SqlParameter("@state", SqlDbType.Int, 4)
				};
				sqlParameters[0].Value = memberData.MemberId;
				sqlParameters[1].Value = memberData.MemberName;
				sqlParameters[2].Value = null;
				if (memberData.MemberPass != null && memberData.MemberPass.Length > 0)
					sqlParameters[2].Value = memberData.MemberPass;
				sqlParameters[3].Value = memberData.RealName;
				sqlParameters[4].Value = memberData.Phone;
				sqlParameters[5].Value = memberData.Introduction;
				sqlParameters[6].Value = memberData.Address;
				sqlParameters[7].Value = memberData.Email;
				sqlParameters[8].Value = memberData.GroupId;
				sqlParameters[9].Value = memberData.AvailableState;

				SqlParameter sqlParam;
				SqlCommand cmd = new SqlCommand();
				cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
				cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
				nRet = SqlHelper.ExecuteNonQuery(cmd, "Member_Update", sqlParameters);
				int.TryParse(cmd.Parameters["@return"].Value.ToString(), out nRet);
				cmd.Parameters.Clear();
			}
			catch {
				nRet = -10;
			}
			return nRet;
		}

		public int AddMember(UserData memberData)
		{
			int nRet = -10;
			try
			{
				SqlParameter[] sqlParameters = {
					new SqlParameter("@MemberName", SqlDbType.NVarChar),
					new SqlParameter("@MemberPass", SqlDbType.NVarChar),
					new SqlParameter("@groupid", SqlDbType.Int, 4)
				};
				sqlParameters[0].Value = memberData.MemberName;
				sqlParameters[1].Value = memberData.MemberPass;
				sqlParameters[2].Value = memberData.GroupId;

				SqlParameter sqlParam;
				SqlCommand cmd = new SqlCommand();
				if (memberData.Email != null && memberData.Email.Length > 0)
				{
					sqlParam = new SqlParameter("@Email", SqlDbType.NVarChar);
					sqlParam.Value = memberData.Email;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.Question != null && memberData.Question.Length > 0)
				{
					sqlParam = new SqlParameter("@Question", SqlDbType.NVarChar);
					sqlParam.Value = memberData.Question;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.Answer != null && memberData.Answer.Length > 0)
				{
					sqlParam = new SqlParameter("@Answer", SqlDbType.NVarChar);
					sqlParam.Value = memberData.Answer;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.RealName != null && memberData.RealName.Length > 0)
				{
					sqlParam = new SqlParameter("@RealName", SqlDbType.NVarChar);
					sqlParam.Value = memberData.RealName;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.Birthday != null && memberData.Birthday.Length > 0)
				{
					sqlParam = new SqlParameter("@Birthday", SqlDbType.NVarChar);
					sqlParam.Value = memberData.Birthday;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.Province != null && memberData.Province.Length > 0)
				{
					sqlParam = new SqlParameter("@Province", SqlDbType.NVarChar);
					sqlParam.Value = memberData.Province;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.City != null && memberData.City.Length > 0)
				{
					sqlParam = new SqlParameter("@City", SqlDbType.NVarChar);
					sqlParam.Value = memberData.City;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.Phone != null && memberData.Phone.Length > 0)
				{
					sqlParam = new SqlParameter("@Phone", SqlDbType.NVarChar);
					sqlParam.Value = memberData.Phone;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.Address != null && memberData.Address.Length > 0)
				{
					sqlParam = new SqlParameter("@Address", SqlDbType.NVarChar);
					sqlParam.Value = memberData.Address;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.Postcode != null && memberData.Postcode.Length > 0)
				{
					sqlParam = new SqlParameter("@Postcode", SqlDbType.NVarChar);
					sqlParam.Value = memberData.Postcode;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.Introduction != null && memberData.Introduction.Length > 0)
				{
					sqlParam = new SqlParameter("@Introduction", SqlDbType.NVarChar);
					sqlParam.Value = memberData.Introduction;
					cmd.Parameters.Add(sqlParam);
				}
				if (memberData.AvailableState != 0)
				{
					sqlParam = new SqlParameter("@State", SqlDbType.Int, 4);
					sqlParam.Value = memberData.AvailableState;
					cmd.Parameters.Add(sqlParam);
				}
				cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
				cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
				nRet = SqlHelper.ExecuteNonQuery(cmd, "Member_ADD", sqlParameters);
				int.TryParse(cmd.Parameters["@return"].Value.ToString(), out nRet);
				cmd.Parameters.Clear();
			}
			catch
			{
				nRet = -10;
			}
			return nRet;
		}

		public List<UserData> GetAllMemberData(int nPageIndex, int nPageSize, out int outTotal, string nameMatch, string mdmcMatch)
		{
			List<UserData> list = new List<UserData>();
			outTotal = 0;
			SqlParameter[] sqlParamenter = { 
				new SqlParameter("@pageIndex",SqlDbType.Int, 4),
				new SqlParameter("@pageSize",SqlDbType.Int, 4),
				new SqlParameter("@namematch",SqlDbType.NVarChar, 20),
				new SqlParameter("@mdmcmatch",SqlDbType.NVarChar, 20),
				new SqlParameter("@outTotalSize",SqlDbType.Int, 4)
            };
			sqlParamenter[0].Value = nPageIndex;
			sqlParamenter[1].Value = nPageSize;
			sqlParamenter[2].Value = null;
			sqlParamenter[3].Value = null;
			sqlParamenter[4].Value = outTotal;
			sqlParamenter[4].Direction = ParameterDirection.Output;
			if(nameMatch != null && nameMatch.Length > 0)
				sqlParamenter[2].Value = nameMatch;
			if(mdmcMatch != null && mdmcMatch.Length > 0)
				sqlParamenter[3].Value = mdmcMatch;

			SqlCommand cmd = new SqlCommand();
			SqlDataReader read = SqlHelper.ExecuteReader(cmd, "GetAllMember", sqlParamenter);
			bool bNextRDS = true;
			do
			{
				while (read != null && read.Read())
				{
					UserData info = new UserData();
					int	nID = 0;
					string strName = read["MemberName"].ToString();
					int.TryParse(read["MemberId"].ToString(), out nID);
					if ((strName != null && strName.Length >0) || nID != 0)
					{
						info.MemberId = nID;
						info.MemberName = strName;
						info.RealName = read["RealName"].ToString();
						info.Email = read["Email"].ToString();
						info.Phone = read["Phone"].ToString();
						if (info.Email != null && info.Email.Length > 0)
						{
							if (info.Phone != null){
								info.Phone += "/";
							}
							info.Phone += info.Email;
						}
						info.Address = read["Address"].ToString();
						info.Introduction = read["Introduction"].ToString();
						info.RoleName = read["Name"].ToString();
						DateTime dt;
						strName = read["DateTime"].ToString();
						if (DateTime.TryParse(strName, out dt))
							info.AddDate = dt;
						int nState;
						strName = read["State"].ToString();
						info.StateName = @"正常";
						if (int.TryParse(strName, out nState) && nState > 10)
							info.StateName = @"关闭";
						info.GroupId = int.Parse(read["GroupId"].ToString());
						list.Add(info);
						bNextRDS = false;
					}
				}
			} while (read != null && bNextRDS && read.NextResult());//这里是关键－－－移动到下一个结果集
			read.Close();
			read.Dispose();
			int.TryParse(cmd.Parameters["@outTotalSize"].Value.ToString(), out outTotal);
			cmd.Dispose();
			return list;
		}
		public List<UserData> GetRoleMembers(int nRoleID)
		{
			List<UserData> list = new List<UserData>();
			SqlParameter[] sqlParamenter = { 
				new SqlParameter("@roleid",SqlDbType.Int, 4),
            };
			sqlParamenter[0].Value = nRoleID;

			SqlCommand cmd = new SqlCommand();
			SqlDataReader read = SqlHelper.ExecuteReader(cmd, "GetRoleMembers", sqlParamenter);
			bool bNextRDS = true;
			do
			{
				while (read != null && read.Read())
				{
					UserData info = new UserData();
					int nID = 0;
					string strName = read["MemberName"].ToString();
					int.TryParse(read["MemberId"].ToString(), out nID);
					if ((strName != null && strName.Length > 0) || nID != 0)
					{
						info.MemberId = nID;
						info.MemberName = strName;
						info.RealName = read["RealName"].ToString();
						info.Email = read["Email"].ToString();
						info.Phone = read["Phone"].ToString();
						if (info.Email != null && info.Email.Length > 0)
						{
							if (info.Phone != null)
							{
								info.Phone += "/";
							}
							info.Phone += info.Email;
						}
						info.Address = read["Address"].ToString();
						info.Introduction = read["Introduction"].ToString();
						DateTime dt;
						strName = read["DateTime"].ToString();
						if (DateTime.TryParse(strName, out dt))
							info.AddDate = dt;

						int nState;
						strName = read["State"].ToString();
						info.StateName = @"正常";
						if (int.TryParse(strName, out nState) && nState > 10)
							info.StateName = @"关闭";
						info.AvailableState = nState;

						nState = 0;
						strName = read["GroupId"].ToString();
						int.TryParse(strName, out nState);
						info.GroupId = nState;

						list.Add(info);
						bNextRDS = false;
					}
				}
			} while (read != null && bNextRDS && read.NextResult());//这里是关键－－－移动到下一个结果集
			read.Close();
			read.Dispose();
			return list;
		}
	}
}
